package com.xuan_poi.apache_poi;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;

/**
 * @author renzhengxuan
 * @date 2021/4/12
 */
public class ExcelRead {
    @Test
    public static void readExcel() throws Exception {
        String path = "D:\\excel\\统测App导入参考表.xlsx";
        String a = null;
        File file = new File(path);
        //首先要读取这个文件，就要用到文件流
        InputStream fileInputStream = new FileInputStream(file);
        Workbook workbook = null;
        if (file.getName().endsWith(".xls")) {
            //03版本
            workbook = new HSSFWorkbook(fileInputStream);
        } else if (file.getName().endsWith(".xlsx")) {
            // 07版本
            workbook = new XSSFWorkbook(fileInputStream);
        }
        Sheet sheet = workbook.getSheetAt(0);
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            int cellCount = rowTitle.getPhysicalNumberOfCells();//获取到总列数
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    System.out.print(cell.getStringCellValue() + " | ");
                }
            }
            System.out.println();
        }
        int rowCount = sheet.getPhysicalNumberOfRows();//总行数
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            //获取每一行的数据
            Row row = sheet.getRow(rowNum);
            if (row != null) {
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    cancelNumberPoint(row.getCell(0));
                    System.out.print(convertCellToString(cell) + " | ");
                }
                System.out.println();
            }
        }
        fileInputStream.close();
    }

    /**
     * 由于excel中的值类型不确定，所以我们写一个统一类型转换的方法
     *
     * @param cell
     * @return
     */
    public static String convertCellToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String cellValue = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC://数字型
                //数字型又分为日期型和普通数字
                // 日期型
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    cellValue = DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd");
                } else {
                    //正常输出数字
//                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    //读取到的数字有可能会是.0的结构，去掉.0
                    DecimalFormat df = new DecimalFormat("#.####################");
                    cellValue = String.valueOf(df.format(cell.getNumericCellValue()));
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                cellValue = null;
                break;
            case Cell.CELL_TYPE_STRING:
                cellValue = cell.getStringCellValue();
                break;
        }
        return cellValue;
    }

    /**
     * 如果读取的某一列类型是数字，并且不想要小数点
     *
     * @return
     */
    public static String cancelNumberPoint(Cell cell) {
        String val = null;
        if (cell == null) {
            return "";
        }
        DecimalFormat df = new DecimalFormat();
        val = df.format(cell.getNumericCellValue());
        return val;
    }
}
